The Aggregate FX node is used create a new column based on an aggregation of multiple given numeric columns. This function supports the following numeric column types: Integer, Float 32, Float 64, and BigInteger.
The Aggregate FX function differs from defining aggregation from Data Model, in which case the aggregation type can be set for each measure and is used in the querying process only. Using the Aggregate FX node in the Data Flow creates a new aggregated column that is part of the schema of the underlying database.
The Aggregate FX node is useful if end users have a need to analyze aggregations of multiple columns. For example, there may be a need for a column summing the cost and overhead to show all expenses.
This can be creating by inputting both of those columns in the Aggregate FX node and setting the aggregation type to Sum.
- Click here to learn more about aggregate functions.
Configure Aggregate FX
Connect the Aggregation FX node to the Select tables of the relevant table. Go to the Properties panel and set the aggregation preferences:
- Select Column: choose the columns that want to apply the aggregation to.
- Function Type: set the type of aggregation. That can be Sum, Average, Minimum, or Maximum.
- Column Output: determine whether or not to keep the original column.
- New Column Name: name the new column.
Function Type
The Aggregate FX node enables the following aggregate functions:
- Sum: returns the sum of values in each row from the given numeric columns.
- Average: returns the average values for each row of the given numeric columns.
- Minimum: returns the lowest values in each row from the given numeric columns.
- Maximum: returns the highest values in each row from the given numeric columns.
Split Nodes
The Aggregate Fx node can be used to produce 1 aggregated column. If you want to create multiple aggregated columns based on a single source table, you can connect multiple Aggregate Fx nodes to the relevant table, thereby 'splitting' the table node.
You can then use the merge function to combine the original table with the new aggregated columns. See the final example below for a demonstration of this process.
Example. Sum Business Expenses
In this example, we have 2 columns showing business expenses: Operating Expenses and Overhead Expenses.
We want to create a new column that will show total business expenses.
To do this, we will Sum the Operating Expenses and Overhead Expenses columns to produce a new column called Total Expenses:
Example. Get the Average
In this example, our table contains a Sales column for each month of the year.
We want to aggregate these sales values to produce a new column showing the Average value for each row (i.e., the average for the year).
To do this, we aggregate all the Sales columns by the Average function, producing a new column which shows the average value for each row:
Example. Get the Maximum
In this example, we have a table with 4 columns showing Returns; each Returns column shows data from a different store.
We want to aggregate these Returns values to create a new column that will show us the maximum Returns value in each row.
To do this, we aggregate each Returns column by the Maximum function. The new column shows the maximum return value from all given columns for each row:
Example. Get the Minimum, Maximum, and Average
Here we have several columns showing test scores from 5 different tests.
For each row, we want to see both the lowest and highest test scores, as well as the average from all tests.
We'll start by aggregating all the test score columns by the Minimum function, to add the new Min Test Score column to the table:
To do this, we'll connect the Aggregate Fx node to the table node, and input all test score columns (green arrow below). We then select the Minimum function, and make sure to keep the original columns (yellow highlight). The new Min Test Score column is then visible, alongside the original columns, in the Preview (blue highlight):
Next, we want to add a second aggregated columns showing the Maximum test scores:
To do this, we'll add a second Aggregate Fx node to the original table, this time aggregating the test score columns by the Maximum function, and being sure not to keep the original columns (yellow highlight).
We now have 2 aggregated tables; one showing all original columns and a new Minimum column, and the second showing only the key column and the new Maximum column:
We now need to join our 2 aggregated tables to produce a single table containing the original columns, and both the Min and Max score columns.
To combine the 2 tables, we'll add a Join Node to the flow, connecting it to both Aggregate nodes and adding a Left Outer Join (green arrow below).
The output is a table showing the columns from both the Aggregate nodes (blue arrow):
Finally, we want to add a third aggregated column showing the Average value for each row. This should result in the following table, which shows the original columns plus the Minimum, Maximum, and Average values: .
To achieve this, we connect a third Aggregate node to the Join node, and aggregate the test score columns using the Average function, and making sure to keep the original columns. The resulting table shows all the columns from the Join, and adds a new Average Test Score column:
